package com.mapgis.utils

import java.io.{FileInputStream, FileReader, Reader}
import java.{lang, util}
import java.sql.{Clob, Connection, Date, DriverManager, PreparedStatement, SQLException, Timestamp}
import java.util.Properties

import com.mapgis.constant.Constants
import com.mapgis.pojo.{ImpEvent, ImpPeople, SqrInfo}
import org.slf4j.LoggerFactory

import scala.collection.mutable.ListBuffer

object JDBCUtil {


  val logger = LoggerFactory.getLogger("com.mapgis.JDBCUtil")
  val properties = new Properties()
  val config = PropertiesUtil.load("config.properties")

  /**
    * 获取连接
    * @param url
    * @param user
    * @param password
    * @return
    */
  def getConnection(url: String, user: String, password: String): Connection = {
    var conn: Connection = null
    try {
      Class.forName("oracle.jdbc.OracleDriver");
      conn = DriverManager.getConnection(url, user, password);

    } catch {
      case e: ClassNotFoundException => {
        e.printStackTrace()
        System.out.println("加载数据库驱动失败")
      }
      case e: Exception => {
        e.printStackTrace()
        System.out.println("连接数据库失败")
      }
    }
    conn
  }

    /**
    * 龙岗这边的连接
    * @return
    */
    def getConnFromLG() ={
        val url=config.getProperty("jdbc.url")
        val user=config.getProperty("jdbc.lggzw.user")
        val password=config.getProperty("jdbc.lggzw.password")

        getConnection(url, user, password)
    }


    /**
      * 提交查询sql语句
      * @param sql
      * @return
      */
    def handExecuteQuery(sql: String) = {
        var list = new ListBuffer[util.HashMap[String, String]]
        val connection = getConnFromLG
        println(s"本次执行的sql是： ${sql}")
        val pstmt = connection.prepareStatement(sql)

        val resultSet = pstmt.executeQuery()
        //获取表的元信息
        val metaData = resultSet.getMetaData

        //将结果集中的数据取出来放到Map中,最后组合成list返回
        while (resultSet.next()) {
            val hashMap = new util.HashMap[String, String]()
            for (i <- 1 to metaData.getColumnCount) {
                val columnName = metaData.getColumnName(i)
                var columnValue = ""
                if (resultSet.getObject(columnName) == null) {
                    columnValue = null
                } else {
                    columnValue = resultSet.getObject(columnName).toString.replaceAll("'", "\\\\'").replaceAll("\n", "\\\\n").replaceAll("\r", "\\\\r").replace("\"","\\\"")
                    //columnValue = resultSet.getObject(columnName).toString.replace("\"","&quot;")
                }
                hashMap.put(columnName, columnValue)
            }
            list.:+=(hashMap)
        }

//        println(list)
        pstmt.close()
        resultSet.close()
        connection.close()
        list.toList
    }


    /**
      * 提交更新sql语句
      * @param sql
      * @return
      */
    def handExecuteUpdate(sql: String)  = {
        var updateNum = 0
        val conn = getConnFromLG()
        var pstmt: PreparedStatement = null
        var flag=0
        try {
            conn.setAutoCommit(false)
            pstmt = conn.prepareStatement(sql)
            updateNum = pstmt.executeUpdate()
            conn.commit()
        }catch {
            case e : SQLException => {
                conn.rollback()
                flag=1
                println("提交普通更新语句出错了，出错sql为：\n" + sql)
                println("错误编号: " + e.getErrorCode + "  错误原因: " + e.getSQLState)
                e.printStackTrace()
            }
            case e : Exception => {
                conn.rollback()
                flag=1
                println("提交普通更新语句出错了，出错sql为：\n" + sql)
                e.getStackTrace
            }
        }finally {
            conn.setAutoCommit(false)
            if (pstmt!=null) {
                pstmt.close()
            }
            if (conn!=null) {
                conn.close()
            }
            if (flag==1){
                println("999999UpdateError")
                //System.exit(1)
            }
        }
        updateNum
    }

    /**
      * 提交更新sql语句
      * @param sql
      * @return
      */
    def handExecuteClobUpdate(sql: String,logPath: String)  = {
        var updateNum = 0
        val conn = getConnFromLG()
        var pstmt: PreparedStatement = null
        try {
            pstmt = conn.prepareStatement(sql)
            val reader = new FileReader(logPath)
//            new FileInputStream(logPath)
            pstmt.setClob(1,reader)
            updateNum = pstmt.executeUpdate()
            reader.close()
            conn.commit()
        }catch {
            case e : Exception => {
                conn.rollback()
                //flag=1
                println("提交Clob更新语句出错了，出错sql为：\n" + sql)
                //println(sql)
                e.getStackTrace
            }
        }finally {
            if (pstmt!=null) {
                pstmt.close()
            }
            if (conn!=null) {
                conn.close()
            }
        }
        updateNum
    }



    /**
      * 自定义的left join方法用来比对数据库中的数据
      * @param selectField
      * @param thisTable
      * @param joinTable
      * @param joinField
      * @param whereStatement
      * @return
      */
    def leftJoin(selectField: String,thisTable: String, joinTable: String, joinStament: String, whereStatement: String) ={
        val sql = s"select ${selectField} from ${thisTable} a left join ${joinTable} b on ${joinStament} where ${whereStatement}"
        sql
    }

    /**
      * 拼接mergeinto语句
      * @param targetTable 目标表
      * @param matchSql 需要插入的数据所在的临时表
      * @param joinField 匹配字段
      * @param updateList set子句的字段
      * @param insertList insert子句的字段
      */
    def mergeInto(targetTable: String, matchSql: String, mergeCondition: String, updateList:List[String], insertList:List[String]) ={
        //获取set子句与insert子句
        //0: update
        //1: insert
        val updateStmt = getStatement(updateList,"update")
        val insertStmt = getStatement(insertList,"insert")

        val sql=
            s"""
              |MERGE INTO ${targetTable} t1
              |USING ( ${matchSql} ) t2 on (${mergeCondition})
              |${updateStmt}
              |${insertStmt}
            """.stripMargin

//        logger.info(sql)
        var mergeNum=0
        mergeNum = handExecuteUpdate(sql)
        println(sql)
        mergeNum
    }



    def mergeEvent(targetTable: String, joinField: String ,insertValue: ImpEvent) ={

        val matchSql = s"select '${insertValue.id}' as id from dual"

        val insertStmt=
            s"""
               |WHEN NOT MATCHED
               |THEN
               |    INSERT VALUES(
               |        '${insertValue.id}',
               |        '${insertValue.gridid}',
               |        '${insertValue.buildingid}',
               |        to_date('${insertValue.reporteddate}','yyyy-MM-dd hh24:mi:ss'),
               |        '${insertValue.creator}',
               |        '${insertValue.acceptdepartment}',
               |        ${insertValue.source},
               |        ${insertValue.kind},
               |        ${insertValue.comenum},
               |        ${insertValue.subkind},
               |        ${insertValue.aim},
               |        ${insertValue.threekind},
               |        '${insertValue.districtcode}',
               |        '${insertValue.location}',
               |        '${insertValue.place}',
               |        ${insertValue.reportednum},
               |        ${insertValue.amount},
               |        ${insertValue.emergency},
               |        '${insertValue.content}',
               |        ${insertValue.ishouse},
               |        '${insertValue.housecode}',
               |        ${insertValue.isgroup},
               |        '${insertValue.gatherer}',
               |        '${insertValue.gatherid}',
               |        '${insertValue.houseid}',
               |        '${insertValue.photosrc}',
               |        to_date(${insertValue.syndate},'yyyy-MM-dd hh24:mi:ss'),
               |        to_date(${insertValue.readtime},'yyyy-MM-dd hh24:mi:ss'),
               |        ${insertValue.isread},
               |        '${insertValue.remark}',
               |        '${insertValue.gisp_x}',
               |        '${insertValue.gisp_y}',
               |        '${insertValue.togetherpeople}',
               |        '${insertValue.houseaddress}',
               |        '${insertValue.levels}'
               |    )
             """.stripMargin
        val sql="MERGE INTO " +  targetTable + " t1 \n" +
                "USING (" + matchSql + ") t2 on (t1." + joinField + "=t2." + joinField + ") \n" +
                insertStmt

        //        logger.info(sql)
        var mergeNum=0
        mergeNum = handExecuteUpdate(sql)


        //println(sql)
        mergeNum
    }

    def mergeEvent(targetTable: String, joinField: String ,insertValueList: List[ImpEvent]) ={

        val matchSql = s"select ? as id from dual"

        val insertStmt=
            s"""
               |WHEN NOT MATCHED
               |THEN
               |    INSERT VALUES(
               |        ?,
               |        ?,
               |        ?,
               |        to_date(?,'yyyy-MM-dd hh24:mi:ss'),
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        sysdate,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?,
               |        ?
               |    )
             """.stripMargin
        val sql="MERGE INTO " +  targetTable + " t1 \n" +
                "USING (" + matchSql + ") t2 on (t1." + joinField + "=t2." + joinField + ") \n" +
                insertStmt

        //        logger.info(sql)
        var mergeNum=0
        mergeNum = handExecuteEventBatchUpdate(sql,insertValueList)
        //println(sql)
        mergeNum
    }



    def handExecuteEventBatchUpdate(sql: String , eventList: List[ImpEvent])={
        var updateNum = 0
        val conn = getConnFromLG()
        //var pstmt: PreparedStatement = null
        val pstmt = conn.prepareStatement(sql)
        try {
            for (elem <- eventList) {
                println(s"本次同步的数据ID为: ${elem.id}")

                pstmt.setString(1, elem.id);
                pstmt.setString(2, elem.id);
                if (elem.gridid != null && !"null".equals(elem.gridid)) {
                    pstmt.setString(3, elem.gridid);
                } else {
                    pstmt.setNull(3, -9);
                }
                if (elem.buildingid != null && !"null".equals(elem.buildingid)) {
                    pstmt.setString(4, elem.buildingid);
                } else {
                    pstmt.setNull(4, -9);
                }
                if (elem.reporteddate != null && !"null".equals(elem.reporteddate)) {
                    pstmt.setString(5, elem.reporteddate);
                } else {
                    pstmt.setNull(5, -9);
                }
                if (elem.creator != null && !"null".equals(elem.creator)) {
                    pstmt.setString(6, elem.creator);
                } else {
                    pstmt.setNull(6, -9);
                }
                if (elem.acceptdepartment != null && !"null".equals(elem.acceptdepartment)) {
                    pstmt.setString(7, elem.acceptdepartment);
                } else {
                    pstmt.setNull(7, -9);
                }
                if (elem.source != null && !"null".equals(elem.source)) {
                    pstmt.setInt(8, elem.source.toInt);
                } else {
                    pstmt.setNull(8, 2);
                }
                if (elem.kind != null && !"null".equals(elem.kind)) {
                    pstmt.setInt(9, elem.kind.toInt);
                } else {
                    pstmt.setNull(9, 2);
                }
                if (elem.comenum != null && !"null".equals(elem.comenum)) {
                    pstmt.setInt(10, elem.comenum.toInt);
                } else {
                    pstmt.setNull(10, 2);
                }
                if (elem.source != null && !"null".equals(elem.subkind)) {
                    pstmt.setInt(11, elem.subkind.toInt);
                } else {
                    pstmt.setNull(11, 2);
                }
                if (elem.aim != null && !"null".equals(elem.aim)) {
                    pstmt.setInt(12, elem.aim.toInt);
                } else {
                    pstmt.setNull(12, 2);
                }
                if (elem.threekind != null && !"null".equals(elem.threekind)) {
                    pstmt.setInt(13, elem.threekind.toInt);
                } else {
                    pstmt.setNull(13, 2);
                }
                if (elem.districtcode != null && !"null".equals(elem.districtcode)) {
                    pstmt.setString(14, elem.districtcode);
                } else {
                    pstmt.setNull(14, -9);
                }
                if (elem.location != null && !"null".equals(elem.location)) {
                    pstmt.setString(15, elem.location);
                } else {
                    pstmt.setNull(15, -9);
                }
                if (elem.place != null && !"null".equals(elem.place)) {
                    pstmt.setString(16, elem.place);
                } else {
                    pstmt.setNull(16, -9);
                }

                if (elem.reportednum != null && !"null".equals(elem.reportednum)) {
                    pstmt.setInt(17, elem.reportednum.toInt);
                } else {
                    pstmt.setNull(17, 2);
                }
                if (elem.amount != null && !"null".equals(elem.amount)) {
                    pstmt.setInt(18, elem.amount.toInt);
                } else {
                    pstmt.setNull(18, 2);
                }
                if (elem.emergency != null && !"null".equals(elem.emergency)) {
                    pstmt.setInt(19, elem.emergency.toInt);
                } else {
                    pstmt.setNull(19, 2);
                }

                if (elem.content != null && !"null".equals(elem.content)) {
                    pstmt.setString(20, elem.content);
                } else {
                    pstmt.setNull(20, -9);
                }

                if (elem.ishouse != null && !"null".equals(elem.ishouse)) {
                    pstmt.setInt(21, elem.ishouse.toInt);
                } else {
                    pstmt.setNull(21, 2);
                }
                if (elem.housecode != null && !"null".equals(elem.housecode)) {
                    pstmt.setString(22, elem.housecode);
                } else {
                    pstmt.setNull(22, -9);
                }

                if (elem.isgroup != null && !"null".equals(elem.isgroup)) {
                    pstmt.setInt(23, elem.isgroup.toInt);
                } else {
                    pstmt.setNull(23, 2);
                }
                if (elem.gatherer != null && !"null".equals(elem.gatherer)) {
                    pstmt.setString(24, elem.gatherer);
                } else {
                    pstmt.setNull(22, -9);
                }
                if (elem.gatherid != null && !"null".equals(elem.gatherid)) {
                    pstmt.setString(25, elem.gatherid);
                } else {
                    pstmt.setNull(25, -9);
                }
                if (elem.houseid != null && !"null".equals(elem.houseid)) {
                    pstmt.setString(26, elem.houseid);
                } else {
                    pstmt.setNull(26, -9);
                }
                if (elem.photosrc != null && !"null".equals(elem.photosrc)) {
                    pstmt.setString(27, elem.photosrc);
                } else {
                    pstmt.setNull(27, -9);
                }

                pstmt.setNull(28, 91);  //readdate,数据在数据库中被读取之后才会改变此值
                if (elem.isread != null && !"null".equals(elem.isread)) {
                    pstmt.setInt(29, elem.isread.toInt);
                } else {
                    pstmt.setNull(29, 2);
                }

                if (elem.remark != null && !"null".equals(elem.remark)) {
                    pstmt.setString(30, elem.remark);
                } else {
                    pstmt.setNull(30, -9);
                }
                if (elem.gisp_x != null && !"null".equals(elem.gisp_x)) {
                    pstmt.setString(31, elem.gisp_x);
                } else {
                    pstmt.setNull(31, -9);
                }
                if (elem.gisp_y != null && !"null".equals(elem.gisp_y)) {
                    pstmt.setString(32, elem.gisp_y);
                } else {
                    pstmt.setNull(32, -9);
                }
                if (elem.togetherpeople != null && !"null".equals(elem.togetherpeople)) {
                    pstmt.setString(33, elem.togetherpeople);
                } else {
                    pstmt.setNull(33, -9);
                }
                if (elem.houseaddress != null && !"null".equals(elem.houseaddress)) {
                    pstmt.setString(34, elem.houseaddress);
                } else {
                    pstmt.setNull(34, -9);
                }
                if (elem.levels != null && !"null".equals(elem.levels)) {
                    pstmt.setString(35, elem.levels);
                } else {
                    pstmt.setNull(35, -9);
                }

                pstmt.addBatch()
            }

            val ints = pstmt.executeBatch()
            conn.commit()
            ints.foreach(print(_))
            updateNum = ints.reduce(_+_)
//                updateNum = updateNum + pstmt.executeUpdate()
        }catch {
            case e : SQLException => {
                conn.rollback()
                println("提交普通更新语句出错了，出错数据为：\n" )
                println("错误编号: " + e.getErrorCode + "  错误原因: " + e.getSQLState)
                e.printStackTrace()
            }
            case e : Exception => {
                conn.rollback()
                println("出错了，未知错误！")
                e.printStackTrace()
            }
        }finally {
            if (pstmt!=null) {
                pstmt.close()
            }
            if (conn!=null) {
                conn.close()
            }
        }
        updateNum
    }



    def mergePeopleSql(targetTable: String, joinField: String ,insertValue: ImpPeople) ={

        val matchSql = s"select '${insertValue.id}' as id from dual"

        val updateSql =
            s"""
              |WHEN MATCHED
              |THEN
              |	UPDATE SET
              |	t1.name='${insertValue.name}',
              |	t1.ismain=${insertValue.ismain},
              |	t1.mobile='${insertValue.mobile}',
              |	t1.cardno='${insertValue.cardno}',
              |	t1.phone='${insertValue.phone}',
              |	t1.sex=${insertValue.sex},
              |	t1.age=${insertValue.age},
              |	t1.address='${insertValue.address}',
              |	t1.email='${insertValue.email}',
              |	t1.credate=to_date(${insertValue.credate},'yyyy-MM-dd hh24:mi:ss'),
              |	t1.modifydate=to_date(sysdate,'yyyy-MM-dd hh24:mi:ss'),
              |	t1.creator='${insertValue.creator}',
              |	t1.modirier='${insertValue.modirier}',
              |	t1.conversationid='${insertValue.conversationid}',
              |	t1.tag_mdimporttag=${insertValue.tag_mdimporttag},
              |	t1.stype=${insertValue.stype},
              |	t1.readtime=to_date(${insertValue.readtime},'yyyy-MM-dd hh24:mi:ss')
            """.stripMargin

        val insertStmt=
            s"""
               |WHEN NOT MATCHED
               |THEN
               |    INSERT VALUES(
               |		'${insertValue.id}',
               |		'${insertValue.name}',
               |		${insertValue.ismain},
               |		'${insertValue.mobile}',
               |		'${insertValue.cardno}',
               |		'${insertValue.phone}',
               |		${insertValue.sex},
               |		${insertValue.age},
               |		'${insertValue.address}',
               |		'${insertValue.email}',
               |		to_date(${insertValue.credate},'yyyy-MM-dd hh24:mi:ss'),
               |		to_date(${insertValue.modifydate},'yyyy-MM-dd hh24:mi:ss'),
               |		'${insertValue.creator}',
               |		'${insertValue.modirier}',
               |		'${insertValue.conversationid}',
               |		${insertValue.tag_mdimporttag},
               |		${insertValue.stype},
               |		${insertValue.isread},
               |		to_date(${insertValue.readtime},'yyyy-MM-dd hh24:mi:ss')
               |	)
             """.stripMargin
        val sql="MERGE INTO " +  targetTable + " t1 \n" +
                "USING (" + matchSql + ") t2 on (t1." + joinField + "=t2." + joinField + ") \n" +
                updateSql +"\n"+ insertStmt

//        //        logger.info(sql)
//        var mergeNum=0
//        mergeNum = handExecuteUpdate(sql)
//        //println(sql)
//        mergeNum
        sql
    }

    def mergePeople(impPeopleList: List[ImpPeople]) ={


        var peopleImportNum = 0
        val conn = getConnFromLG()
        try {
            impPeopleList.foreach{
                impPeople=>{
                    var pstmt: PreparedStatement = null
                    var sql=mergePeopleSql(Constants.TABLE_LG_IMP_PERSON,"id",impPeople)
                    pstmt = conn.prepareStatement(sql)
                    try{
                        peopleImportNum = peopleImportNum + pstmt.executeUpdate()
                    }catch {
                        case e : SQLException => {
                            conn.rollback()
                            println("提交普通更新语句出错了，出错sql为：\n" + sql)
                            println("错误编号: " + e.getErrorCode + "  错误原因: " + e.getSQLState)
                            e.printStackTrace()
                        }
                        case e : Exception => {
                            conn.rollback()
                            println("提交普通更新语句出错了，出错sql为：\n" + sql)
                            e.getStackTrace
                        }
                    }
                    println("导入第n条人员信息的时间:" + peopleImportNum + "---->" + DateUtils.getLocalDateTime())
                    pstmt.close()
                }
            }

            conn.commit()
        }catch {
            case e : Exception => {
                conn.rollback()
                println("出错了，出现了未知错误！")
                e.getStackTrace
            }
        }finally {
            if (conn!=null) {
                conn.close()
            }
        }
        peopleImportNum
    }


    def getMergeCondition(equalList: List[String]) ={
        var equalConditions = ""
        if (equalList != null) {
            if (equalList.size != 0) {
                val stmt = equalList.map{
                    field => {
                        val str = "t1." + field + "=t2." + field
                        str
                    }
                }
                equalConditions = stmt.mkString(" and ")
            }
        }
        equalConditions
    }

    /**
      * 拼接更新语句与插入语句
      * @param fieldList
      * @param optition
      * @return
      */
    def getStatement(fieldList: List[String], optition: String) ={
        var statement = ""
        if (fieldList != null) {
            if (fieldList.size != 0) {
                optition match {
                    case "update" => {
                        val stmt = fieldList.map{
                            field => {
                                val str = "t1." + field + "=t2." + field
                                str
                            }
                        }.mkString(",")
                        statement = "WHEN MATCHED THEN UPDATE SET "+ stmt + " \n"
                    }
                    case "insert" => {
                        val stmt = fieldList.map{
                            field => {
                                val str = "t2." + field
                                str
                            }
                        }.mkString(",")
                        statement = "WHEN NOT MATCHED THEN INSERT VALUES(" + stmt +")"
                    }
                }
            }
        }

        statement
    }

    /**
      * 通过表明获取表的所有字段
      * @param tableName
      */
    def getFieldFromTableName(tableName: String)={
        var list = new ListBuffer[String]
        val connection = getConnFromLG
        val sql = s"select * from ${tableName} where 1=0"
        println(sql)
        val pstmt = connection.prepareStatement(sql)
        val resultSet = pstmt.executeQuery()
        //获取结果的元信息
        val metaData = resultSet.getMetaData

        for (i <- 1 to metaData.getColumnCount) {
            val columnName = metaData.getColumnName(i)
            list.+=(columnName)
        }
        //println(list)
        pstmt.close()
        resultSet.close()
        connection.close()

        list.toList
    }

    /**
      * 通过id删除需要删除的数据
      * @param idList
      * @param tableName
      * @return
      */
    def deleteById(idList: List[String], tableName: String) = {
        var deleteNum = 0
        if (idList.size!=0) {
            for (i <- 0 to idList.size/Constants.ORACLE_CURSOR){
                var newIdList = idList.slice(i*Constants.ORACLE_CURSOR, (i + 1) * Constants.ORACLE_CURSOR)

                val sql=s"delete from ${tableName} where id in (${newIdList.mkString(",")})"
                deleteNum += handExecuteUpdate(sql)
            }
        }
        deleteNum
    }

    /**
      * 通过id修改某一个字段的值
      * @param tableName
      * @param fieldName
      * @param fieldValue
      * @param id
      */
    def updateFieldById(tableName: String, fieldName: String, fieldValue:String, id: String): Unit ={
        val sql = s"update ${tableName} set ${fieldName}=${fieldValue} where id=${id}"
        val updateNum = handExecuteUpdate(sql)
    }

    /**
      * 获得一个表里面的记录数
      * @param tableName
      */
    def getNumTable(tableName: String) ={
        val sql = s"select count(*) count from ${tableName}"
        val count = handExecuteQuery(sql).head.get("count".toUpperCase).toInt
        count
    }


    def main(args: Array[String]): Unit = {
    //    val value = "linestring(141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141083.2163937833 43893.387958228,141078.0001062556 43999.4878141834,141077.3934131805 43999.0554679947,141077.3934131805 43999.0554679947,141077.3934131805 43999.0554679947,141077.3934131805 43999.0554679947,141077.3934131805 43999.0554679947,141077.3934131805 43999.0554679947,141077.3934131805 43999.0554679947,141084.8907225625 44019.6215439226,141083.9509056855 44020.5227561604,141078.7693674437 44028.7994056653,141076.2511365057 44031.8299095623,141075.937155302 44032.1672118742,141075.8324949033 44032.2796459764,141075.6253011562 44032.3938191041,141075.6253011562 44032.3938191041,141075.6253011562 44032.3938191041,141075.6253011562 44032.3938191041,141075.6253011562 44032.3938191041,141075.6253011562 44032.3938191041,141075.6253011562 44032.3938191041,141075.6253011562 44032.3938191041,141075.6253011562 44032.3938191041,141066.602799918 44037.8584762706,141065.571084744 44038.2079513534,141065.1609510263 44038.2149072908,141065.1609510263 44038.2149072908,141065.1609510263 44038.2149072908,141065.1609510263 44038.2149072908,141060.0134488023 44044.7204308811,141057.6577471671 44049.9613558852,141058.0806424858 44049.2902297083,141061.8207654909 44046.6816406508,141061.8207654909 44046.6816406508,141061.8207654909 44046.6816406508,141061.8207654909 44046.6816406508,141061.8207654909 44046.6816406508,141061.8207654909 44046.6816406508,141061.8207654909 44046.6816406508,141082.2363200733 44003.0677125907,141015.4416531272 44001.212720244,141014.9311082195 44001.1107190438,141014.8285739127 44001.1124578199,141010.6187453648 44006.8274579516,141010.6187453648 44006.8274579516,141010.6187453648 44006.8274579516,141010.6187453648 44006.8274579516,141008.8267508474 44009.4030036861,141008.8267508474 44009.4030036861,141008.8267508474 44009.4030036861,141008.8267508474 44009.4030036861,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,141001.9715542883 44030.1018277365,140984.936686846 44015.1197448316,140984.936686846 44015.1197448316,140984.936686846 44015.1197448316,140984.936686846 44015.1197448316,140984.936686846 44015.1197448316)"
    //    val value = "linestring(141083.2163937833 43893.387958228,141083.2163937833 43893.387958228)"
    //    println(getDistince(value))
//        mergeInto("sss","aaa","id",List("name","age"),List("id","name","age"))
//        mergeInto("sss","aaa","id",null,List("id","name","age"))
//        mergeInto("sss","aaa","id",List("name","age"),null)
//        val fields = getFieldFromTableName("lgsjwq_ads.sync_tmp_build")
//        println(fields.length)
//        val updateList = fields.filter(_.toLowerCase!="id")
//        mergeInto("gzw_building","select * from lgsjwq_ads.sync_tmp_build","id",updateList,fields)
//        println(getMergeCondition(List("id", "aa")))
//        var str=
//            """
//              |aaa'aaa''aaa
//              | rrrr
//              | "ssss
//            """.stripMargin
//        var str2=str.replaceAll("'", "\\\\'").replaceAll("\n", "\\\\n").replaceAll("\r", "\\\\r").replace("\"","\\\"")
//        println(str)
//        println(str2)
        var str3="{\\\"creator\\\":null,\\\"fileCount\\\":null,\\\"height\\\":0,\\\"isScan\\\":null,\\\"name\\\":\\\"2020_11_16_09_28_32_2bd605e39a1041fc8e2b4eabedfab2d4.bmp\\\",\\\"nameedPath\\\":\\\"defaultren/NOW/rappnew/2020/11/16\\\",\\\"showName\\\":\\\"2020_11_16_09_28_32_2bd605e39a1041fc8e2b4eabedfab2d4\\\",\\\"size\\\":2410787,\\\"time\\\":0,\\\"title\\\":\\\"2020_11_16_09_28_32_2bd605e39a1041fc8e2b4eabedfab2d4\\\",\\\"userName\\\":null,\\\"uuid\\\":null,\\\"viewName\\\":null,\\\"width\\\":0}"
        val str4 = str3.replaceAll("\\\\\\\"","\"")
        println(str3)
        println(str4)
        println(str3.replaceAll("""\\\"""","""""""))
    }






}
